package controllers;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;

import beans.Entity;
import beans.File;
import beans.Group;
import beans.Module;
import beans.Render;
import beans.Right;
import beans.Status;
import beans.User;
import util.HibernateUtil;

/**
 * Abstract controller class that uses Hibernate for data persistence
 * 
 * @author 266945
 */
public class AbstractController {

	// logging
	private static Logger logger = Logger.getLogger(AbstractController.class
			.getName());

	public void launchSession() {
		Session s = HibernateUtil.getSessionFactory().openSession();
		s.beginTransaction();
		s.flush();
	}

	/**
	 * @param o
	 *            {@link Object} object to persist
	 */
	public void create(Object o) {

		logger.info("create new " + o.getClass().getSimpleName());

		Session s = HibernateUtil.getSessionFactory().openSession();
		s.beginTransaction();

		try {
			s.save(o);
		} catch (Exception e) {
			e.printStackTrace();
			s.getTransaction().rollback();
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!s.getTransaction().wasCommitted()) {
			s.flush();
			s.getTransaction().commit();
		}
		s.close();
	}

	/**
	 * SQL DELETE
	 * 
	 * @param o
	 *            {@link Object}
	 * @param id
	 *            {@link int}
	 * @return
	 */
	public Object delete(Object o, long id) {

		logger.info("deletion of " + o.getClass().getSimpleName());

		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();

		o = session.load(o.getClass(), id);

		try {
			session.delete(o);
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!session.getTransaction().wasCommitted()) {
			session.flush();
			session.getTransaction().commit();
		}
		session.close();

		return o;
	}

	/**
	 * Returns an ArrayList<{@link Object}> containing all the persisted beans
	 * 
	 * @param object
	 * @return ArrayList
	 */
	@SuppressWarnings("rawtypes")
	public ArrayList list(Object o) {

		logger.info("listing " + o.getClass().getSimpleName());

		ArrayList list = new ArrayList();
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();

		try {
			list = (ArrayList) session.createQuery(
					"from " + o.getClass().getName() + " ").list();
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!session.getTransaction().wasCommitted()) {
			session.flush();
			session.getTransaction().commit();
		}
		session.close();

		return list;
	}

	/**
	 * Updates given bean on the database
	 * 
	 * @param o
	 * @return Object
	 */
	public Object update(Object o) {

		logger.info("update of " + o.getClass().getSimpleName());

		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();

		try {
			session.merge(o);
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
			o = null;
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!session.getTransaction().wasCommitted()) {
			session.flush();
			session.getTransaction().commit();
		}

		session.close();

		return o;

	}

	/**
	 * This function replaces special characters by their ASCII code.<br />
	 * <br />
	 * 
	 * @param sData
	 *            : The {@link String} to treat. <br />
	 * @return the treated {@link String}. <code>Null</code> otherwise.
	 */
	public static String replaceSpecialCharacters(String sData) {
		String sReturn = sData;

		if (sReturn != null) {
			final String PLAIN_ASCII = "AaEeIiOoUu" // grave
					+ "AaEeIiOoUuYy" // acute
					+ "AaEeIiOoUuYy" // circumflex
					+ "AaOoNn" // tilde
					+ "AaEeIiOoUuYy" // umlaut
					+ "Aa" // ring
					+ "Cc" // cedilla
					+ "OoUu" // double acute
					+ "sS"; // S

			final String UNICODE = "\u00C0\u00E0\u00C8\u00E8\u00CC\u00EC\u00D2\u00F2\u00D9\u00F9"
					+ "\u00C1\u00E1\u00C9\u00E9\u00CD\u00ED\u00D3\u00F3\u00DA\u00FA\u00DD\u00FD"
					+ "\u00C2\u00E2\u00CA\u00EA\u00CE\u00EE\u00D4\u00F4\u00DB\u00FB\u0176\u0177"
					+ "\u00C3\u00E3\u00D5\u00F5\u00D1\u00F1"
					+ "\u00C4\u00E4\u00CB\u00EB\u00CF\u00EF\u00D6\u00F6\u00DC\u00FC\u0178\u00FF"
					+ "\u00C5\u00E5"
					+ "\u00C7\u00E7"
					+ "\u0150\u0151\u0170\u0171" + "\u0161\u0160";

			StringBuilder sb = new StringBuilder();
			int n = sData.length();
			for (int i = 0; i < n; i++) {
				char c = sData.charAt(i);
				int pos = UNICODE.indexOf(c);
				if (pos > -1) {
					sb.append(PLAIN_ASCII.charAt(pos));
				} else {
					sb.append(c);
				}
			}

			sReturn = replaceLigature(sb.toString());
		}

		return sReturn;
	}

	/**
	 * This function replace special characters by ASCII codes.<br />
	 * <br />
	 * 
	 * @param str
	 *            : The {@link String} to treat. <br />
	 * @return The treated {@link String}. <code>null</code> otherwise.
	 */
	public static String replaceLigature(String str) {

		if (str == null) {
			return null;
		}

		str = str.trim();

		if (str.length() > 0) {
			final String PLAIN_ASCII = "OE" + "oe" // e dans le o
					+ "AE" + "ae";

			final String UNICODE = "\u0152" + "\u0153" + "\u00C6" + "\u00E6";

			// remove accentued from a string and replace with ascii equivalent

			StringBuilder sb = new StringBuilder();
			int n = str.length();
			for (int i = 0; i < n; i++) {
				char c = str.charAt(i);
				int pos = UNICODE.indexOf(c);
				if (pos > -1) {
					System.out.println(PLAIN_ASCII.charAt(pos));
					sb.append(PLAIN_ASCII.charAt(pos * 2) + ""
							+ PLAIN_ASCII.charAt(((pos * 2) + 1)));
				} else {
					sb.append(c);
				}
			}
			return sb.toString();
		} else {
			return "";
		}
	}

	/**
	 * Reads data from the current Hibernate session.
	 * 
	 * @param o
	 * @param id
	 * @return
	 */
	public Object readFromSession(Object o, long id) {

		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();

		try {
			o = session.load(o.getClass(), id);

		} catch (HibernateException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!session.getTransaction().wasCommitted()) {
			session.flush();
			session.getTransaction().commit();
		}

		return o;
	}

	/**
	 * Reads data from the model, instanciates a new Hibernate session.
	 * 
	 * @param o
	 * @param id
	 * @return
	 */
	public Object readFromDB(Object o, int id) {

		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();

		try {

			o = session.get(o.getClass(), id);

		} catch (HibernateException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		// commit only if tx still hasn't been committed yet (by hibernate)
		if (!session.getTransaction().wasCommitted()) {
			session.flush();
			session.getTransaction().commit();
		}

		return o;
	}

	// public People getPeople(String s) {
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	// People result = null;
	// try {
	// List<Object[]> queryResult;
	// String request =
	// "select p.ALSTOM_ID, p.FIRST_NAME, p.MIDDLE_NAME, p.LAST_NAME, p.EMPLOYEE_TYPE, p.REF_MANAGER_ID, p.SECTOR, "
	// +
	// "p.SUB_SECTOR, p.PRIMARY_LOCATION, mg.FIRST_NAME as MANAGER_FN, mg.LAST_NAME as MANAGER_LN , am.EMAIL, am.QUOTA, p.CONTRACT_COUNTRY_CODE , p.STATUS, "
	// +
	// "p.JOB_TITLE, p.BUSINESS, p.COST_CENTER, p.BUSINESS_UNIT_CODE, p.BUSINESS_UNIT_NAME "
	// + "from webidmint.PEOPLE p "
	// +
	// "left join webidmint.ACCOUNTS_MAILBOX am on p.ALSTOM_ID = am.REF_OWNER_ID and am.TO_DELETE = 'FALSE' "
	// + "left join webidmint.PEOPLE mg on p.REF_MANAGER_ID = mg.ALSTOM_ID "
	// + "where p.ALSTOM_ID = '"+s+"' ";
	// SQLQuery query = session.createSQLQuery(request);
	// queryResult = query.list();
	// if(queryResult.size() == 1){
	// result = new People();
	// result = fillPeople(queryResult.get(0));
	// }
	//
	// } catch (HibernateException e) {
	// System.out.println(e.getMessage());
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// if (!session.getTransaction().wasCommitted()) {
	// session.flush();
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//

	// public People fillPeople(Object[] obj) {
	// People result = new People();
	// if (obj != null) {
	// if (obj[0] == null) {
	// result.setAlstomId(-1);
	// } else {
	// result.setAlstomId(Integer.parseInt((obj[0].toString())));
	// }
	// // mettre des try catch
	// if (obj[1] == null)
	// result.setFirstName("");
	// else
	// result.setFirstName(obj[1].toString());
	//
	// if (obj[2] == null)
	// result.setMiddleName("");
	// else
	// result.setMiddleName(obj[2].toString());
	//
	// if (obj[3] == null)
	// result.setLastName("");
	// else
	// result.setLastName(obj[3].toString());
	//
	// result.setDisplayName(obj[1].toString() + " " + obj[3].toString());
	//
	// if (obj[4] == null)
	// result.setEmployeeType("");
	// else
	// result.setEmployeeType(obj[4].toString());
	//
	// if (obj[5] == null)
	// result.setRefManagerId(-1);
	// else
	// result.setRefManagerId(Integer.parseInt((obj[5].toString())));
	//
	// if (obj[6] == null)
	// result.setSector("");
	// else
	// result.setSector(obj[6].toString());
	//
	// if (obj[7] == null)
	// result.setSubSector("");
	// else
	// result.setSubSector(obj[7].toString());
	//
	// if (obj[8] == null)
	// result.setPrimaryLocation("");
	// else
	// result.setPrimaryLocation(obj[8].toString());
	//
	// if (obj[9] == null)
	// result.setManagerFirstName("");
	// else
	// result.setManagerFirstName(obj[9].toString());
	//
	// if (obj[10] == null)
	// result.setManagerLastName("");
	// else
	// result.setManagerLastName(obj[10].toString());
	//
	// if (obj[11] == null)
	// result.setEmail("");
	// else
	// result.setEmail(obj[11].toString());
	//
	// if (obj[12] == null)
	// result.setQuota("");
	// else
	// result.setQuota(obj[12].toString());
	//
	// if (obj[13] == null)
	// result.setRefCountryCode("");
	// else
	// result.setRefCountryCode(obj[13].toString());
	//
	// if (obj[14] == null)
	// result.setStatus("");
	// else
	// result.setStatus(obj[14].toString());
	//
	// if (obj[15] == null)
	// result.setJobTitle("");
	// else
	// result.setJobTitle(obj[15].toString());
	//
	// if (obj[16] == null)
	// result.setBusiness("");
	// else
	// result.setBusiness(obj[16].toString());
	//
	// if (obj[17] == null)
	// result.setCostCenter("");
	// else
	// result.setCostCenter(obj[17].toString());
	//
	// if (obj[18] == null)
	// result.setBusinessUnitCode("");
	// else
	// result.setBusinessUnitCode(obj[18].toString());
	//
	// if (obj[19] == null)
	// result.setBusinessUnitName("");
	// else
	// result.setBusinessUnitName(obj[19].toString());
	// }
	// return result;
	// }

	// public List<Application> getAllApplications(){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// List<Application> result = new ArrayList<Application>();
	// try {
	//
	// String request =
	// "select ac.DISPLAY_NAME, aa.APP_NAME, AVG(ar.USER_RATE), aa.TAGS, aa.APP_ID "
	// +"from APPSTORE_APPLICATIONS aa "
	// +"left join APPSTORE_CATEGORY ac on aa.REF_CATEGORY_ID = ac.CATEGORY_ID "
	// +"left join APPSTORE_RATINGS ar on aa.APP_ID = ar.REF_APP_ID "
	// +"group by ac.DISPLAY_NAME, aa.APP_NAME, aa.TAGS, aa.APP_ID " ;
	//
	// SQLQuery query = session.createSQLQuery(request);
	// List<Object[]> tmp = query.list();
	// for(int i = 0; i<tmp.size(); i++){
	// result.add(fillApplication(tmp.get(i)));
	// }
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// // commit only if tx still hasn't been committed yet (by hibernate)
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//
	// public List<Application> getApplications(String search, String category){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// List<Application> result = new ArrayList<Application>();
	// search = search.trim();
	// category = category.trim();
	// try {
	//
	// String request =
	// "select ac.DISPLAY_NAME, aa.APP_NAME, AVG(ar.USER_RATE), aa.TAGS, aa.APP_ID "
	// +"from APPSTORE_APPLICATIONS aa "
	// +"left join APPSTORE_CATEGORY ac on aa.REF_CATEGORY_ID = ac.CATEGORY_ID "
	// +"left join APPSTORE_RATINGS ar on aa.APP_ID = ar.REF_APP_ID "
	// +"where (lower(aa.APP_NAME) like '%"+ search.toLowerCase() +"%' "
	// +"or lower(aa.TAGS) like '%"+ search.toLowerCase() +"%' ) ";
	// if(category != null && category.toLowerCase().equals("") == false){
	// request += "and " + category + " ";
	// }
	// request += "group by ac.DISPLAY_NAME, aa.APP_NAME, aa.TAGS, aa.APP_ID ";
	//
	// SQLQuery query = session.createSQLQuery(request);
	// List<Object[]> tmp = query.list();
	// for(int i = 0; i<tmp.size(); i++){
	// result.add(fillApplication(tmp.get(i)));
	// }
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// // commit only if tx still hasn't been committed yet (by hibernate)
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//
	// public Application fillApplication(Object[] obj){
	// Application result = new Application();
	//
	// if(obj != null){
	// if(obj[0] != null)
	// result.setCategory(obj[0].toString());
	// else
	// result.setCategory("");
	// if(obj[1] != null)
	// result.setName(obj[1].toString());
	// else
	// result.setName("");
	// if(obj[2] != null)
	// result.setRating(Float.parseFloat((obj[2].toString())));
	// else
	// result.setRating(0);
	// if(obj[3] != null)
	// result.setTags(obj[3].toString());
	// else
	// result.setTags("");
	// if(obj[4] != null)
	// result.setAppId(obj[4].toString());
	// else
	// result.setAppId("");
	// }
	// return result;
	// }
	//
	// public List<ApplicationComment> getApplicationComments(String search){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// List<ApplicationComment> result = new ArrayList<ApplicationComment>();
	// search = search.trim();
	// try {
	//
	// String request =
	// "select to_char(cmt.COMMENT_DATE, 'DD/MM/YYYY HH24:MI'), p.FIRST_NAME, p.LAST_NAME, p.ALSTOM_ID, cmt.REF_APP_ID, cmt.USER_COMMENT "
	// +"from APPSTORE_COMMENTS cmt "
	// +"left join PEOPLE p on p.ALSTOM_ID = cmt.REF_USER_ID "
	// +"where cmt.REF_APP_ID = '"+ search +"' "
	// +"order by cmt.COMMENT_DATE desc ";
	//
	//
	// SQLQuery query = session.createSQLQuery(request);
	// List<Object[]> tmp = query.list();
	// for(int i = 0; i<tmp.size(); i++){
	// result.add(fillApplicationComment(tmp.get(i)));
	// }
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// // commit only if tx still hasn't been committed yet (by hibernate)
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//
	// public ApplicationComment fillApplicationComment(Object[] obj){
	// ApplicationComment result = new ApplicationComment();
	//
	// if(obj != null){
	// if(obj[0] != null)
	// result.setDate(obj[0].toString());
	// else
	// result.setDate("");
	// if(obj[1] != null && obj[2] != null)
	// result.setRefUser(obj[1].toString() + ' ' + obj[2].toString());
	// else
	// result.setRefUser("");
	//
	// if(obj[3] != null)
	// result.setRefUserId(Integer.parseInt((obj[3].toString())));
	// else
	// result.setRefUserId(0);
	// if(obj[4] != null)
	// result.setRefAppId(obj[4].toString());
	// else
	// result.setRefAppId("");
	// if(obj[5] != null)
	// result.setUserComment(obj[5].toString());
	// else
	// result.setUserComment("");
	// }
	// return result;
	// }
	//
	// public List<ApplicationPackage> getApplicationPackages(String search){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// List<ApplicationPackage> result = new ArrayList<ApplicationPackage>();
	// try {
	//
	// String request =
	// "select PACKAGE_ID, REF_APP_ID, VERSION, LANGUAGE, INSTALL_ID, UNINSTALL_ID, RELEASE_DATE "
	// +"from APPSTORE_APP_PACKAGE "
	// +"where REF_APP_ID = '"+ search +"' ";
	//
	// SQLQuery query = session.createSQLQuery(request);
	// List<Object[]> tmp = query.list();
	// for(int i = 0; i<tmp.size(); i++){
	// result.add(fillApplicationPackage(tmp.get(i)));
	// }
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// // commit only if tx still hasn't been committed yet (by hibernate)
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//
	// public ApplicationPackage fillApplicationPackage(Object[] obj){
	// ApplicationPackage result = new ApplicationPackage();
	//
	// if(obj != null){
	// if(obj[0] != null)
	// result.setPackageId(obj[0].toString());
	// else
	// result.setPackageId("");
	// if(obj[1] != null)
	// result.setRefAppId(obj[1].toString());
	// else
	// result.setRefAppId("");
	// if(obj[2] != null)
	// result.setVersion(obj[2].toString());
	// else
	// result.setVersion("");
	// if(obj[3] != null)
	// result.setLanguage(obj[3].toString());
	// else
	// result.setLanguage("");
	// if(obj[3] != null)
	// result.setInstallId(obj[3].toString());
	// else
	// result.setInstallId("");
	// if(obj[4] != null)
	// result.setUninstallId(obj[4].toString());
	// else
	// result.setUninstallId("");
	// if(obj[5] != null)
	// result.setReleaseDate(obj[5].toString());
	// else
	// result.setReleaseDate("");
	// }
	// return result;
	// }
	//
	// public ApplicationRating getApplicationRating(String search){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// ApplicationRating result = new ApplicationRating();
	//
	// if(search != null){
	// try{
	//
	// String request =
	// "select sum(CASE WHEN USER_RATE = 0 THEN 1 ELSE 0 END) AS NB_0, "
	// +"sum(CASE WHEN USER_RATE = 1 THEN 1 ELSE 0 END) AS NB_1, "
	// +"sum(CASE WHEN USER_RATE = 2 THEN 1 ELSE 0 END) AS NB_2, "
	// +"sum(CASE WHEN USER_RATE = 3 THEN 1 ELSE 0 END) AS NB_3, "
	// +"sum(CASE WHEN USER_RATE = 4 THEN 1 ELSE 0 END) AS NB_4, "
	// +"sum(CASE WHEN USER_RATE = 5 THEN 1 ELSE 0 END) AS NB_5, "
	// +"avg(USER_RATE) as AVGRATING "
	// +"from APPSTORE_RATINGS "
	// +"where REF_APP_ID = '"+search+"' ";
	// SQLQuery query = session.createSQLQuery(request);
	// List<Object[]> tmp = query.list();
	//
	// result = fillApplicationRating(tmp.get(0));
	//
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	// }
	//
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	// return result;
	// }
	//
	// public ApplicationRating fillApplicationRating(Object [] obj){
	// ApplicationRating result = new ApplicationRating();
	//
	// if(obj != null){
	// if(obj[0] != null)
	// result.setZeroStar(Integer.parseInt((obj[0].toString())));
	// else
	// result.setZeroStar(0);
	// if(obj[1] != null)
	// result.setOneStar(Integer.parseInt((obj[1].toString())));
	// else
	// result.setOneStar(0);
	// if(obj[2] != null)
	// result.setTwoStars(Integer.parseInt((obj[2].toString())));
	// else
	// result.setTwoStars(0);
	// if(obj[3] != null)
	// result.setThreeStars(Integer.parseInt((obj[3].toString())));
	// else
	// result.setThreeStars(0);
	// if(obj[4] != null)
	// result.setFourStars(Integer.parseInt((obj[4].toString())));
	// else
	// result.setFourStars(0);
	// if(obj[5] != null)
	// result.setFiveStars(Integer.parseInt((obj[5].toString())));
	// else
	// result.setFiveStars(0);
	// if(obj[6] != null)
	// result.setRate(Float.parseFloat((obj[6].toString())));
	// else
	// result.setRate(0);
	// }
	// return result;
	// }
	//
	// public List<ApplicationCategory> getApplicationCategories(){
	// Session session = HibernateUtil.getSessionFactory().openSession();
	// session.beginTransaction();
	//
	// List<ApplicationCategory> result = new ArrayList<ApplicationCategory>();
	//
	// try{
	//
	// String request = "select CATEGORY_ID, DISPLAY_NAME "
	// +"from APPSTORE_CATEGORY ";
	// SQLQuery query = session.createSQLQuery(request);
	//
	// List<Object[]> tmp = query.list();
	// for(int i = 0; i<tmp.size(); i++){
	// result.add(fillApplicationCategory(tmp.get(i)));
	// }
	//
	//
	// } catch (HibernateException e) {
	// e.printStackTrace();
	// session.getTransaction().rollback();
	// }
	//
	// if (!session.getTransaction().wasCommitted()) {
	// session.getTransaction().commit();
	// }
	//
	// return result;
	// }
	//
	// public ApplicationCategory fillApplicationCategory(Object [] obj){
	// ApplicationCategory result = new ApplicationCategory();
	//
	// if(obj != null){
	// if(obj[0] != null)
	// result.setCategoryId(obj[0].toString());
	// else
	// result.setCategoryId("");
	// if(obj[1] != null)
	// result.setDisplayName(obj[1].toString());
	// else
	// result.setDisplayName("");
	// }
	// return result;
	// }

	public boolean sendApplicationComment(String refAppId, String refUserId,
			String comment) {
		boolean result = false;
		if (refAppId != null && refUserId != null && refAppId != ""
				&& refUserId != "" && comment != null && comment != "") {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();

			try {
				comment = comment.trim();

				DateFormat dateFormat = new SimpleDateFormat(
						"yyyy/MM/dd HH:mm:ss");
				Date dateTmp = new Date();

				String date = dateFormat.format(dateTmp);

				String request = "INSERT INTO APPSTORE_COMMENTS (REF_APP_ID, REF_USER_ID, USER_COMMENT, COMMENT_DATE) "
						+ "VALUES ('"
						+ refAppId
						+ "', '"
						+ refUserId
						+ "', '"
						+ comment
						+ "', to_date('"
						+ date
						+ "', 'YYYY/MM/DD HH24:MI:SS') ) ";
				SQLQuery query = session.createSQLQuery(request);
				int tmp = query.executeUpdate();
				if (tmp > 0)
					result = true;
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}

		}
		return result;
	}

	public boolean removeApplicationComment(String refAppId, String refUserId,
			String commentDate) {
		boolean result = false;
		if (refAppId != null && !refAppId.isEmpty() && refUserId != null
				&& !refUserId.isEmpty() && commentDate != null
				&& !commentDate.isEmpty()) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String removeComment = "DELETE FROM APPSTORE_COMMENTS "
						+ "WHERE REF_APP_ID = '" + refAppId + "' "
						+ "AND REF_USER_ID ='" + refUserId + "' "
						+ "AND to_char(COMMENT_DATE, 'DD/MM/YYYY HH24:MI') = '"
						+ commentDate + "' ";
				SQLQuery query = session.createSQLQuery(removeComment);
				int tmp = query.executeUpdate();
				if (tmp >= 0)
					result = true;

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public boolean sendApplicationRating(String refAppId, String refUserId,
			int userRating) {
		boolean result = false;
		if (refAppId != null && refUserId != null && refAppId != ""
				&& refUserId != "" && userRating > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();

			try {
				// We first check if the user has already voted for this
				// application. If no, we insert
				// the vote in the table. Else we replace his old vote.

				String checkAlready = "SELECT * FROM APPSTORE_RATINGS "
						+ "WHERE REF_APP_ID = '" + refAppId + "' "
						+ "AND REF_USER_ID = '" + refUserId + "' ";
				SQLQuery queryCheck = session.createSQLQuery(checkAlready);
				List<Object[]> tmpCheck = queryCheck.list();
				if (tmpCheck.size() > 0) {
					// User already voted for this app
					String replaceVote = "UPDATE APPSTORE_RATINGS "
							+ "SET USER_RATE = " + userRating + " "
							+ "WHERE REF_APP_ID = '" + refAppId + "' "
							+ "AND REF_USER_ID = '" + refUserId + "' ";
					SQLQuery queryReplace = session.createSQLQuery(replaceVote);
					int tmp = queryReplace.executeUpdate();
					if (tmp > 0)
						result = true;
				} else {
					// User first vote for this app
					String request = "INSERT INTO APPSTORE_RATINGS (REF_APP_ID, REF_USER_ID, USER_RATE) "
							+ "VALUES ('"
							+ refAppId
							+ "', '"
							+ refUserId
							+ "', " + userRating + " ) ";
					SQLQuery query = session.createSQLQuery(request);
					int tmp = query.executeUpdate();
					if (tmp > 0)
						result = true;
				}

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}

		}
		return result;
	}

	public User testMyDb(String login, String password) {
		User user = null;
		if (login != null && !login.isEmpty() && password != null
				&& !password.isEmpty()) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select u.sFirstName, u.sLastName, u.sLogin, u.idEntity, u.idRight, u.idStatus, u.sAuthentificationType, u.bIsActive, u.idUser, u.sMail, "
						+ "s.sNameStatus, e.sEntityName "
						+ "from user u "
						+ "left join entity e on e.idEntity = u.idEntity "
						+ "left join status s on s.idStatus = u.idStatus "
						+ "where sLogin = '"
						+ login
						+ "' and sPassword = '"
						+ password + "' ";

				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() == 1) {
					// Si il y a un r�sultat alors l'utilisateur est pr�sent
					// dans la base et ses logs sont bons
					// On remplit un objet User qu'on retournera
					user = new User();
					user = fillUser(tmp.get(0));
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return user;
	}

	public User fillUser(Object[] obj) {
		User result = new User();

		if (obj != null) {
			if (obj[0] != null)
				result.setsFirstName(obj[0].toString());
			else
				result.setsFirstName("");
			if (obj[1] != null)
				result.setsLastName(obj[1].toString());
			else
				result.setsLastName("");
			if (obj[2] != null)
				result.setsLogin(obj[2].toString());
			else
				result.setsLogin("");
			if (obj[3] != null)
				result.setIdEntity(Integer.parseInt((obj[3].toString())));
			else
				result.setIdEntity(0);
			if (obj[4] != null)
				result.setIdRight(Integer.parseInt((obj[4].toString())));
			else
				result.setIdRight(0);
			if (obj[5] != null)
				result.setIdStatus(Integer.parseInt((obj[5].toString())));
			else
				result.setIdStatus(0);
			if (obj[6] != null)
				result.setsAuthentificationType(obj[6].toString());
			else
				result.setsAuthentificationType("");
			if (obj[7] != null)
				result.setbIsActive(Boolean.valueOf(obj[7].toString()));
			else
				result.setbIsActive(false);
			if (obj[8] != null)
				result.setIdUser(Integer.parseInt((obj[8].toString())));
			else
				result.setIdUser(0);
			if (obj[9] != null)
				result.setsMail(obj[9].toString());
			else
				result.setsMail("");
			if (obj[10] != null)
				result.setsStatusName(obj[10].toString());
			else
				result.setsStatusName("");
			if (obj[11] != null)
				result.setsEntityName(obj[11].toString());
			else
				result.setsEntityName("");
		}
		return result;
	}

	public List<Group> getGroupByUserId(int id) {
		List<Group> result = new ArrayList<Group>();
		if (id > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select g.idGroup, g.sGroupName, g.idCorrector, g.idRender, g.idNoteReport, r.sRenderName, u.sLogin "
						+ "from groups g "
						+ "left join ingroup ig on ig.idGroup = g.idGroup "
						+ "left join render r on r.idRender = g.idRender "
						+ "left join user u on u.idUser = g.idCorrector "
						+ "where ig.idUser = " + id;
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillGroup(tmp.get(i)));
						// Pour chaque groupe on rempli la liste des membres
						result.get(i).setMembers(
								getGroupMembersByGroupId(Integer.parseInt(tmp
										.get(i)[0].toString())));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public List<User> getGroupMembersByGroupId(int id) {
		List<User> result = new ArrayList<User>();
		if (id > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select u.sLogin " + "from ingroup ing "
						+ "left join groups gr on gr.idGroup = ing.idGroup "
						+ "left join user u on u.idUser = ing.idUser "
						+ "where gr.idGroup = " + id;
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						User tmpUser = new User();
						Object obj = tmp.get(i);
						if (tmp.get(i) != null)
							tmpUser.setsLogin(obj.toString());
						else
							tmpUser.setsLogin("");
						System.out.println(tmpUser.getsLogin());
						result.add(tmpUser);
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public Group fillGroup(Object[] obj) {
		Group result = new Group();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdGroup(Integer.parseInt((obj[0].toString())));
			else
				result.setIdGroup(0);
			if (obj[1] != null)
				result.setsGroupName(obj[1].toString());
			else
				result.setsGroupName("");
			if (obj[2] != null)
				result.setIdCorrector(Integer.parseInt((obj[2].toString())));
			else
				result.setIdCorrector(0);
			if (obj[3] != null)
				result.setIdRender(Integer.parseInt((obj[3].toString())));
			else
				result.setIdRender(0);
			if (obj[4] != null)
				result.setIdNoteReport(Integer.parseInt((obj[4].toString())));
			else
				result.setIdNoteReport(0);
			if (obj[5] != null)
				result.setsRenderName(obj[5].toString());
			else
				result.setsRenderName("");
			if (obj[6] != null)
				result.setsLogin(obj[6].toString());
			else
				result.setsLogin("");
		}
		return result;
	}

	public List<Module> getModuleByUserId(int id) {
		List<Module> result = new ArrayList<Module>();
		if (id > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select m.idModule, m.sModuleName, m.idUser, m.sEnseirbId, m.sDescription, u.sLogin "
						+ "from module m "
						+ "inner join user u on u.idUser = m.idUser "
						+ "where m.idUser = " + id;
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillModule(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public Module fillModule(Object[] obj) {
		Module result = new Module();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdModule(Integer.parseInt((obj[0].toString())));
			else
				result.setIdModule(0);
			if (obj[1] != null)
				result.setsModuleName(obj[1].toString());
			else
				result.setsModuleName("");
			if (obj[2] != null)
				result.setIdUser(Integer.parseInt((obj[2].toString())));
			else
				result.setIdUser(0);
			if (obj[3] != null)
				result.setsEnseirbId(obj[3].toString());
			else
				result.setsEnseirbId("");
			if (obj[4] != null)
				result.setsDescription(obj[4].toString());
			else
				result.setsDescription("");
			if (obj[5] != null)
				result.setsLogin(obj[5].toString());
			else
				result.setsLogin("");
		}
		return result;
	}

	public List<Render> getRenderByUserId(int id) {
		List<Render> result = new ArrayList<Render>();
		if (id > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select distinct r.idRender, r.sRenderName, r.sDescription, r.dDeadlineLimit, r.dDeadlineEffective, r.sScale, m.sModuleName "
						+ "from render r "
						+ "left join groups gr on gr.idRender = r.idRender "
						+ "left join ingroup ing on ing.idGroup = gr.idGroup "
						+ "left join module m on m.idModule = r.idModule "
						+ "where ing.idUser = " + id;
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillRender(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public Render fillRender(Object[] obj) {
		Render result = new Render();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdRender(Integer.parseInt((obj[0].toString())));
			else
				result.setIdRender(0);
			if (obj[1] != null)
				result.setsRenderName(obj[1].toString());
			else
				result.setsRenderName("");
			if (obj[2] != null)
				result.setsDescription(obj[2].toString());
			else
				result.setsDescription("");
			if (obj[3] != null)
				result.setdDeadlineLimit(obj[3].toString());
			else
				result.setdDeadlineLimit("");
			if (obj[4] != null)
				result.setdDeadlineEffective(obj[4].toString());
			else
				result.setdDeadlineEffective("");
			if (obj[5] != null)
				result.setsScale(obj[5].toString());
			else
				result.setsScale("");
			if (obj[6] != null)
				result.setsModuleName(obj[6].toString());
			else
				result.setsModuleName("");
		}
		return result;
	}

	public List<User> getUsers(String search) {
		List<User> result = new ArrayList<User>();
		if (search != null && !search.isEmpty()) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {
				String request = "select u.sFirstName, u.sLastName, u.sLogin, u.idEntity, u.idRight, u.idStatus, u.sAuthentificationType, u.bIsActive, u.idUser, u.sMail, "
						+ "s.sNameStatus, e.sEntityName "
						+ "from user u "
						+ "left join entity e on e.idEntity = u.idEntity "
						+ "left join status s on s.idStatus = u.idStatus "
						+ "where ("
						+ "lower(u.sFirstName) like '"
						+ search
						+ "%' or "
						+ "lower(u.sLastName) like '"
						+ search
						+ "%' or "
						+ "lower(u.sLogin) like '"
						+ search
						+ "' or "
						+ "lower(u.sMail) = '"
						+ search
						+ "' or "
						+ "lower(e.sEntityName) = '" + search + "') ";
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillUser(tmp.get(i)));
						result.get(i)
								.setRenders(
										getRenderByUserId(Integer.parseInt(tmp
												.get(i)[8].toString())));
						result.get(i)
								.setModules(
										getModuleByUserId(Integer.parseInt(tmp
												.get(i)[8].toString())));
						result.get(i).setGroups(
								getGroupByUserId(Integer.parseInt(tmp.get(i)[8]
										.toString())));
					}

				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

	public boolean removeGroup(int idGroup) {
		boolean result = false;
		if (idGroup > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String removeComment = "DELETE FROM GROUPS "
						+ "WHERE idGroup = '" + idGroup + "' ";
				SQLQuery query = session.createSQLQuery(removeComment);
				int tmp = query.executeUpdate();
				if (tmp >= 0)
					result = true;

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public boolean removeModule(int idModule) {
		boolean result = false;
		if (idModule > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String removeComment = "DELETE FROM module "
						+ "WHERE idModule = " + idModule + " ";
				SQLQuery query = session.createSQLQuery(removeComment);
				int tmp = query.executeUpdate();
				if (tmp >= 0)
					result = true;

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public boolean removeRender(int idRender) {
		boolean result = false;
		if (idRender > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String removeComment = "DELETE FROM RENDER "
						+ "WHERE idModule = '" + idRender + "' ";
				SQLQuery query = session.createSQLQuery(removeComment);
				int tmp = query.executeUpdate();
				if (tmp >= 0)
					result = true;

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public List<File> getFilebyRenderandUserId(int idUser, int idRender) {
		List<File> result = new ArrayList<File>();
		if (idRender > 0 && idUser > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select f.idFile, f.sFileName, f.idGroup, f.bIsCorrected, f.bIsDeposed, f.dDeadlineLimit, f.dDeadlineEffective, f.sDirectory "
						+ "from file  f "
						+ "where f.idGroup in "
						+ "(select gr.idGroup from ingroup ing, groups gr "
						+ "where ing.idUser = "
						+ idUser
						+ " and  gr.idGroup = ing.idGroup and gr.idRender ="
						+ idRender + ")";
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillFile(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public File fillFile(Object[] obj) {
		File result = new File();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdFile(Integer.parseInt(obj[0].toString()));
			else
				result.setIdFile(0);
			if (obj[1] != null)
				result.setsFileName(obj[1].toString());
			else
				result.setsFileName("");
			if (obj[2] != null)
				result.setIdGroup(Integer.parseInt(obj[2].toString()));
			else
				result.setIdGroup(0);
			if (obj[3] != null)
				result.setbIsCorrected((byte) obj[3]);
			else
				result.setbIsCorrected((byte) 1);
			if (obj[4] != null)
				result.setbIsDeposed((byte) obj[4]);
			else
				result.setbIsDeposed((byte) 0);
			if (obj[5] != null)
				result.setdDeadlineLimit(obj[5].toString());
			else
				result.setdDeadlineLimit("2014/01/01 00:00:0");
			if (obj[6] != null)
				result.setdDeadlineEffective(obj[6].toString());
			else
				result.setdDeadlineEffective("2014/01/01 00:00:0");
			if (obj[7] != null)
				result.setsDirectory(obj[7].toString());
			else
				result.setsDirectory("");
			return result;
		} else
			return null;
	}

	public boolean createFile(String name, String destination, int idRender,
			int idUser) {
		if (idRender > 0 && name != null && destination != null) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {
				String find_group_req = "select gr.idGroup from ingroup ing, groups gr"
						+ " where ing.idUser = "
						+ idUser
						+ " and  gr.idGroup = ing.idGroup and gr.idRender ="
						+ idRender;
				SQLQuery query = session.createSQLQuery(find_group_req);
				int idGroup = (int) query.list().get(0);

				DateFormat dateFormat = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				Date date = new Date();

				destination = destination.replace("\\", "\\\\");
				String insert_request = "INSERT INTO `bddenseirb`.`file` (`sFileName`, `idGroup`, `bIsCorrected`, `bIsDeposed`, `dDeadlineLimit`, `dDeadlineEffective`,`sDirectory`) "
						+ "VALUES  ('"
						+ name
						+ "', '"
						+ idGroup
						+ "', false, true, '"
						+ dateFormat.format(date)
						+ "', '"
						+ dateFormat.format(date)
						+ "','"
						+ destination + "');";
				System.out.println(insert_request);
				SQLQuery query_insert = session.createSQLQuery(insert_request);
				query_insert.executeUpdate();
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
				return false;
			}
			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
			return true;
		} else
			return false;
	}

	public String deleteFilebyId(int id) {
		String File_path = null;
		if (id > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {
				File_path = "select sDirectory from file where idFile ='" + id
						+ "'";
				SQLQuery path_query = session.createSQLQuery(File_path);
				File_path = (String) path_query.list().get(0);
				System.out
						.println("This is in Abstract Controller and should return the file path "
								+ File_path);

				String request = "DELETE FROM `bddenseirb`.`file` WHERE `idFile`='"
						+ id + "';";
				SQLQuery query = session.createSQLQuery(request);
				query.executeUpdate();

			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
				return null;
			}
			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
				return File_path;
			}
		}
		return File_path;
	}

	public List<Entity> getEntities() {
		List<Entity> result = new ArrayList<Entity>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		try {

			String request = "select idEntity, sEntityName " + "from entity ";
			SQLQuery query = session.createSQLQuery(request);
			List<Object[]> tmp = query.list();
			if (tmp.size() > 0) {
				for (int i = 0; i < tmp.size(); i++) {
					result.add(fillEntity(tmp.get(i)));
				}
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		if (!session.getTransaction().wasCommitted()) {
			session.getTransaction().commit();
		}

		return result;
	}

	public Entity fillEntity(Object[] obj) {
		Entity result = new Entity();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdEntity(Integer.parseInt((obj[0].toString())));
			else
				result.setIdEntity(0);
			if (obj[1] != null)
				result.setsEntityName(obj[1].toString());
			else
				result.setsEntityName("");
		}
		return result;
	}

	public List<Right> getRights() {
		List<Right> result = new ArrayList<Right>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		try {

			String request = "select idRight, sRightName " + "from rights ";
			SQLQuery query = session.createSQLQuery(request);
			List<Object[]> tmp = query.list();
			if (tmp.size() > 0) {
				for (int i = 0; i < tmp.size(); i++) {
					result.add(fillRight(tmp.get(i)));
				}
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		if (!session.getTransaction().wasCommitted()) {
			session.getTransaction().commit();
		}

		return result;
	}

	public Right fillRight(Object[] obj) {
		Right result = new Right();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdRight(Integer.parseInt((obj[0].toString())));
			else
				result.setIdRight(0);
			if (obj[1] != null)
				result.setsRightName(obj[1].toString());
			else
				result.setsRightName("");
		}
		return result;
	}

	public List<Status> getStatus() {
		List<Status> result = new ArrayList<Status>();
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		try {

			String request = "select idStatus, sNameStatus " + "from status ";
			SQLQuery query = session.createSQLQuery(request);
			List<Object[]> tmp = query.list();
			if (tmp.size() > 0) {
				for (int i = 0; i < tmp.size(); i++) {
					result.add(fillStatus(tmp.get(i)));
				}
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}

		if (!session.getTransaction().wasCommitted()) {
			session.getTransaction().commit();
		}

		return result;
	}

	public Status fillStatus(Object[] obj) {
		Status result = new Status();

		if (obj != null) {
			if (obj[0] != null)
				result.setIdStatus(Integer.parseInt((obj[0].toString())));
			else
				result.setIdStatus(0);
			if (obj[1] != null)
				result.setsNameStatus(obj[1].toString());
			else
				result.setsNameStatus("");
		}
		return result;
	}

	public boolean createUser(String sFirstName, String sLastName,
			String sLogin, String sPassword, String sMail, int idEntity,
			int idRight, int idStatus) {
		boolean result = false;
		System.out.println("yuyuuy");
		System.out.println(sFirstName + ' ' + sLastName + ' ' + sLogin + ' '
				+ sPassword + ' ' + sMail + ' ' + idEntity + ' ' + idStatus
				+ ' ' + idRight);
		if (sFirstName != null && !sFirstName.isEmpty() && sLastName != null
				&& !sLastName.isEmpty() && sLogin != null && !sLogin.isEmpty()
				&& sPassword != null && !sPassword.isEmpty() && sMail != null
				&& !sMail.isEmpty() && idEntity > 0 && idRight > 0
				&& idStatus > 0) {
			System.out.println("yuyuu222y");
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			// IL FAUDRAIT FAIRE UN CHECK ICI QUE LE GARS N'EXISTE PAS DEJA
			try {

				String request = "INSERT INTO user "
						+ "(sFirstName, sLastName, sLogin, sPassword, sMail, idEntity, idRight, idStatus, sAuthentificationType, bIsActive) "
						+ "values " + "('" + sFirstName + "', '" + sLastName
						+ "', '" + sLogin + "', '" + sPassword + "', '" + sMail
						+ "', " + idEntity + ", " + idRight + ", " + idStatus
						+ ", 'BDD', 1) ";
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("User creation executeUpdate() return : "
						+ update);
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}

		}
		return result;
	}

	public boolean createModule(String sModuleName, String sEnseirbId,
			String sDescription, int idOwner) {
		boolean result = false;
		if (sModuleName != null && !sModuleName.isEmpty() && sEnseirbId != null
				&& !sEnseirbId.isEmpty() && sDescription != null
				&& !sDescription.isEmpty() && idOwner > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			// IL FAUDRAIT FAIRE UN CHECK ICI QUE LE MODULE N'EXISTE PAS DEJA
			try {

				String request = "INSERT INTO module "
						+ "(sModuleName, idUser, sEnseirbId, sDescription) "
						+ "values " + "('" + sModuleName + "', " + idOwner
						+ ", '" + sEnseirbId + "', '" + sDescription + "') ";
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("Module creation executeUpdate() return : "
						+ update + "|| =1 means it worked!");
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}

		}
		return result;
	}

	public boolean createRender(String renderName, int idModule,
			String description, String deadlineLimit, String scale) {
		boolean result = false;
		if (renderName != null && !renderName.isEmpty() && description != null
				&& !description.isEmpty() && deadlineLimit != null
				&& !deadlineLimit.isEmpty() && idModule > 0 && scale != null
				&& !scale.isEmpty()) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			// IL FAUDRAIT FAIRE UN CHECK ICI QUE LE MODULE N'EXISTE PAS DEJA
			try {

				String request = "INSERT INTO render "
						+ "(sRenderName, idModule, sDescription, dDeadlineLimit, sScale) "
						+ "values " + "('" + renderName + "', " + idModule
						+ ", '" + description + "', '" + deadlineLimit + "', '"
						+ scale + "') ";
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("Rendu creation executeUpdate() return : "
						+ update + " || =1 means it worked!");
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}

		}
		return result;
	}

	public boolean createGroup(String groupeName, int idCorrector,
			int idRender, int idNoteReport) {
		boolean result = false;
		if (groupeName != null && !groupeName.isEmpty() && idCorrector > 0
				&& idRender > 0 && idNoteReport > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			// IL FAUDRAIT FAIRE UN CHECK ICI QUE LE MODULE N'EXISTE PAS DEJA
			try {
				String request = "INSERT INTO groups "
						+ "(sGroupName, idCorrector, idRender, idNoteReport) "
						+ "values " + "('" + groupeName + "', " + idCorrector
						+ ", " + idRender + ", " + idNoteReport + ") ";
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("Groupe creation executeUpdate() return : "
						+ update + " || =1 means it worked!");
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public boolean addUserToGroup(int idUser, int idGroup) {
		boolean result = false;
		if (idUser > 0 && idGroup > 0) {
			// IL FAUDRAIT VERIFIER QUE LE USER N'EST PAS DEJA DANS LE GROUPE
			// ET QU'IL N'A PAS DEJA UN GROUPE POUR CE RENDU
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();

			try {
				String request = "INSERT INTO ingroup " + "(idUser, idGroup) "
						+ "values " + "(" + idUser + ", " + idGroup + ") ";
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("Assign executeUpdate() return : " + update
						+ " || =1 means it worked!");
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public boolean removeUserFromGroup(int idUser, int idGroup) {
		boolean result = false;
		if (idUser > 0 && idGroup > 0) {
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();

			try {
				String request = "DELETE FROM ingroup " + "WHERE idUser = "
						+ idUser + " " + "AND idGroup = " + idGroup;
				SQLQuery query = session.createSQLQuery(request);
				int update = query.executeUpdate();
				if (update > 0) {
					result = true;
				}
				System.out.println("Assign executeUpdate() return : " + update
						+ " || =1 means it worked!");
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}
		return result;
	}

	public List<Module> searchModule(String search) {
		List<Module> result = new ArrayList<Module>();
		if (search != null && !search.isEmpty()) {
			search = search.trim();
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select distinct m.idModule, m.sModuleName, m.idUser, m.sEnseirbId, m.sDescription, u.sLogin "
						+ "from module m "
						+ "inner join user u on u.idUser = m.idUser "
						+ "where lower(m.sModuleName) like '%"
						+ search.toLowerCase()
						+ "%' "
						+ "or lower(m.sEnseirbId) like '%"
						+ search.toLowerCase() + "%' ";
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillModule(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}
	
	public List<Group> searchGroup(String search) {
		List<Group> result = new ArrayList<Group>();
		if (search != null && !search.isEmpty()) {
			search = search.trim();
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select distinct g.idGroup, g.sGroupName, g.idCorrector, g.idRender, g.idNoteReport, r.sRenderName, u.sLogin "
						+ "from groups g "
						+ "left join ingroup ig on ig.idGroup = g.idGroup "
						+ "left join render r on r.idRender = g.idRender "
						+ "left join user u on u.idUser = g.idCorrector "
						+ "where lower(g.sGroupName) like '%"
						+ search.toLowerCase()
						+ "%' "
						+ "or lower(r.sRenderName) like '%"
						+ search.toLowerCase() + "%' "
						+"or lower(u.sLogin) like '%"
						+ search.toLowerCase() + "%' ";
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillGroup(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}
	
	public List<Render> searchRender(String search) {
		List<Render> result = new ArrayList<Render>();
		if (search != null && !search.isEmpty()) {
			search = search.trim();
			Session session = HibernateUtil.getSessionFactory().openSession();
			session.beginTransaction();
			try {

				String request = "select distinct r.idRender, r.sRenderName, r.sDescription, r.dDeadlineLimit, r.dDeadlineEffective, r.sScale, m.sModuleName "
						+ "from render r "
						+ "left join groups gr on gr.idRender = r.idRender "
						+ "left join ingroup ing on ing.idGroup = gr.idGroup "
						+ "left join module m on m.idModule = r.idModule "
						+ "where lower(r.sRenderName) like '%"
						+ search.toLowerCase()
						+ "%' "
						+ "or lower(m.sModuleName) like '%"
						+ search.toLowerCase() + "%' "
						+"or lower(r.sDescription) like '%"
						+ search.toLowerCase() + "%' ";
				SQLQuery query = session.createSQLQuery(request);
				List<Object[]> tmp = query.list();
				if (tmp.size() > 0) {
					for (int i = 0; i < tmp.size(); i++) {
						result.add(fillRender(tmp.get(i)));
					}
				}
			} catch (HibernateException e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			}

			if (!session.getTransaction().wasCommitted()) {
				session.getTransaction().commit();
			}
		}

		return result;
	}

}
